package com.surelution.projects.crm

import java.text.SimpleDateFormat
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.grails.plugins.excelimport.ExcelImportUtils;

class ExcelUploaderController {

	static Map CONFIG_BOOK_COLUMN_MAP = [
		sheet:"a",
		startRow: 1,
		columnMap:  [
			'F':'stationCode',
			'G':'stationName',
			'H':'manager',
			'I':'address',
			'M':'ton',
			'N':'tonTitle',
			'Q':'star',
			'R':'starTitle',
			'O':'level',
			'P':'levelTitle',
			'AB':'generalArea',
			'AC':'buildingArea',
			'AD':'coverArea',
			'AE':'hallArea',
			'AF':'toiletArea',
			'AG':'parkingArea',
			'AI':'hasShop',
			'S':'managerTel'
		]
	]
	static Map CONFIG_CARD_PLAN_MAP = [
		sheet:"a",
		startRow: 0,
		columnMap:  [
			'A':'stationCode',
			'B':'station',
			'C':'planNumber',
		]
	]
	static Map CONFIG_STATION_MAP = [
		sheet:"station",
		startRow: 2,
		columnMap:  [
			'B':'stationCode',
			'C':'name',
			'D':'address',
			'E':'managerTel',
		]
	]
	
	
	def htmlTableParserService
	def springSecurityService

	def cardPlan = {
		def file = params.cardPlan
		def time
		Date planTime
		Workbook workbook = WorkbookFactory.create(file.inputStream)
		List cardPlanList = ExcelImportUtils.columns(workbook, CONFIG_CARD_PLAN_MAP)
		cardPlanList.each{
			println it
			if (it.get("station") == "站名"){
				time = it.get("planNumber")
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM")
				planTime = sdf.parse(time)
			}
			else{
				OwnStation station = OwnStation.findByCode(it.get("stationCode"))
				if (!CardPlanList.findByStationAndPlanTime(station,planTime)){
					CardPlanList cardPlan = new CardPlanList(stationCode:"${it.get("stationCode")}")
					cardPlan.planTime = planTime
					cardPlan.station = station
					float planNumber = Float.parseFloat("${it.get("planNumber")}")
					cardPlan.planNumber = planNumber
					cardPlan.save()
					println cardPlan
				}
				else{
					cardPlan = CardPlanList.findByStationAndPlanTime(station,planTime)
					float planNumber = Float.parseFloat("${it.get("planNumber")}")
					cardPlan.planNumber = planNumber
					cardPlan.save()
				}
			}
		}
		redirect(action:"index")
	}
	
	def sImport = {
		def file = params.stationFile
		def time
		Workbook workbook = WorkbookFactory.create(file.inputStream)
		List competitorList = ExcelImportUtils.columns(workbook, CONFIG_STATION_MAP)
		println competitorList
		competitorList.each{
			println it
			if (!CompetitorStation.findBySn(it.get("stationCode"))){
				CompetitorStation competitorStationInstance = new CompetitorStation(sn:"${it.get("stationCode")}")
				competitorStationInstance.name = it.get("name")
				competitorStationInstance.address = it.get("address")
				competitorStationInstance.managerTel = it.get("managerTel")
				competitorStationInstance.hasShop=false
				competitorStationInstance.level = Level.get(1)
				competitorStationInstance.star = StarLevel.get(1)
				competitorStationInstance.tonLevel = TonLevel.get(1)
				competitorStationInstance.company = CompetitorCompany.get(1)
				println CompetitorCompany.get(1)
				OwnStation station = User.findByUsername(springSecurityService.principal.username).station
				competitorStationInstance.station = station
				competitorStationInstance.stationArea = station.stationArea
				competitorStationInstance.isLocked = false
				competitorStationInstance.latitude = 0
				competitorStationInstance.longitude = 0
				competitorStationInstance.save()
				println competitorStationInstance
			}
			else{
				println "repeat competitorStation!${it.get(name)}"
			}
		}
		redirect(action:"index")
	}
	
	
    def index = {
		println "index come here"
	}
	
	def upload = {
		def file = params.excelFile
		Workbook workbook = WorkbookFactory.create(file.inputStream)
		
		BufferedReader br = new BufferedReader(new InputStreamReader(file.inputStream, "utf8"))
		def rows = htmlTableParserService.parse(br)
		rows.each {row->
			row.each {word->
				print word
				print ","
			}
			println "\n"
		}
		
//				数据源为txt的导入方式：
		//		Workbook workbook = WorkbookFactory.create(file.inputStream)
		//		int count = workbook.getNumberOfSheets()
		//		count.times {i->
		//			Sheet sheet = workbook.getSheetAt(i)
		//			println sheet.sheetName
		//		}
				
				List bookParamsList = ExcelImportUtils.columns(workbook, CONFIG_BOOK_COLUMN_MAP)
		
				bookParamsList.each {row->
					println row
					OwnStation station = new OwnStation(name:"${row.stationName}", address:"${row.address}",shortName:"${row.stationName}",
						code:"${row.stationCode}", manager:"${row.manager}",isLocked:false,isActived:true,
						tonLevel:loadTonLevel("${row.ton}", "${row.tonTitle}"),star:loadStarLevel("${row.star}", "${row.starTitle}"), level:loadLevel("${row.level}", "${row.levelTitle}"),
						generalArea:"${row.generalArea}", buildingArea:"${row.buildingArea}", coverArea:"${row.coverArea}", hallArea:"${row.hallArea}", toiletArea:"${row.toiletArea}",
						parkingArea:"${row.parkingArea}",longitude:"0",latitude:"0", hasShop:"yes"=="${row.hasShop}", managerTel:"${row.managerTel}",
						)
					station.save(flush:true)
					println station
				}
				
		
		redirect(action:"index")
	}
	
	def csvUpload = {
	}
	
	def uploadPatrol = {
		def file = params.patrol
		BufferedReader reader = new BufferedReader(new InputStreamReader(file.inputStream, "utf-8"))
		
		reader.eachLine {line->
			def l = line.split("\\|")
			println "INSERT INTO RAW_PATROL_INFO VALUES('62b523e9-e386-4789-82a9-cf4f95011008','A110','${l[1]}','${l[0]}','2013-05-20 10:38:58.000000',FALSE)"
			}
		redirect(action:"index")
		
		}
	
	def uploadMarketCardNo = {
		def file = params.marketCard
		Market m = Market.get(params.marketId)
		BufferedReader reader = new BufferedReader(new InputStreamReader(file.inputStream, "utf-8"))
		reader.eachLine {line->
			def l = line.split("\\|")
			MemberCard memberCard = new MemberCard(cardNo:l[0],holderName:m.manager,accountAcceptBranch:m.station.name,isMaster:false,
				connectTime:new Date(),accountAcceptDate:m.startTime,market:m)
			memberCard.save()
		}
		redirect(action:"index")
	}
	
	
	private Level loadLevel(String code, String name) {
		Level l = Level.findByCode(code)
		if(l == null) {
			l = new Level(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
	
	private StarLevel loadStarLevel(String code, String name) {
		StarLevel l = StarLevel.findByCode(code)
		if(l == null) {
			l = new StarLevel(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
	
	private TonLevel loadTonLevel(String code, String name) {
		TonLevel l = TonLevel.findByCode(code)
		if(l == null) {
			l = new TonLevel(code:code, title:name)
			l.save(flush:true)
		}
		return l
	}
}
